Declare @StartDate datetime
Declare @EndDate datetime

SET @StartDate = '30-Apr-2014 6:00 PM'
SET @EndDate = '30-May-2014 6:00 PM'

--select * from tblCaseProfileIndex
--Where CaseSourceID = 1 And IsReplyCase = 0
--And CreatedDate Between @StartDate And @EndDate

select count(CaseRefID) As TotalIncomingAskACRACount from tblCaseProfileIndex
Where CaseSourceID = 1 And IsReplyCase = 0
And CreatedDate Between @StartDate And @EndDate

select count(CaseRefID) As TotalIncomingEmailCount from tblCaseProfileIndex
Where CaseSourceID = 2 And IsReplyCase = 0
And CreatedDate Between @StartDate And @EndDate

Select NoOfWorkingDays, Count(CaseProfileIndex) AskACRAClosedCaseCountByTD  From tblCaseProfileIndex 
Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate
And (EscalatedToTeamID IS NULL OR EscalatedToTeamID IN (Select TeamID from tblTeam Where TeamTypeID = 1))
Group By NoOfWorkingDays
Order By NoOfWorkingDays

Select NoOfWorkingDays, Count(CaseProfileIndex) AskACRAClosedCaseCountTask  From tblCaseProfileIndex 
Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate
And (EscalatedToTeamID IN (Select TeamID from tblTeam Where TeamTypeID = 2))
Group By NoOfWorkingDays
Order By NoOfWorkingDays

Select Count(CaseProfileIndex) AskACRATotalActive  From tblCaseProfileIndex 
Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus <> 100 And CreatedDate Between @StartDate And @EndDate 

Select Count(CaseProfileIndex) AAOver3DClosed  From tblCaseProfileIndex 
Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate And NoOfWorkingDays > 3

--Direct Email
Select NoOfWorkingDays, Count(CaseProfileIndex) DirectEmailClosedCaseCountByTD  From tblCaseProfileIndex 
Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate
And (EscalatedToTeamID IS NULL OR EscalatedToTeamID IN (Select TeamID from tblTeam Where TeamTypeID = 1))
Group By NoOfWorkingDays
Order By NoOfWorkingDays

Select NoOfWorkingDays, Count(CaseProfileIndex) DirectEmailClosedCaseCountTask  From tblCaseProfileIndex 
Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate
And (EscalatedToTeamID IN (Select TeamID from tblTeam Where TeamTypeID = 2))
Group By NoOfWorkingDays
Order By NoOfWorkingDays

Select Count(CaseProfileIndex) DirectEmailTotalActive  From tblCaseProfileIndex 
Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus <> 100 And CreatedDate Between @StartDate And @EndDate 

Select Count(CaseProfileIndex) DEOver3DClosed From tblCaseProfileIndex 
Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate And NoOfWorkingDays > 3
--

Select T.NoOfWorkingDays, Count(T.CaseRefNo) AAEsclatedToACRA
FROM(
Select T.CaseRefNo, T.TaskCaseRefNo, (dbo.funcGetWorkingDays(T.CaseReceivedDate, 
		(select MIN(TH.CreatedDate) from tblTaskHistory TH 
			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
			Where TE.TeamTypeID = 2 And TH.TaskID = T.TaskID)
		)) NoOfWorkingDays
From tblCaseProfileIndex CPI 
INNER JOIN tblTask T ON CPI.CaseProfileIndex = T.CaseProfileIndex
Where CPI.CaseSourceID = 1 And CPI.IsReplyCase = 0 And CPI.CreatedDate Between @StartDate And @EndDate
And T.TaskID IN (Select Top 1 TaskID from tblTaskHistory TH 
			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
			Where TE.TeamTypeID = 2 And TH.TaskID = T.TaskID Order By TaskHistoryID)) T
Group By T.NoOfWorkingDays
Order By T.NoOfWorkingDays

Select T.NoOfWorkingDays, Count(T.CaseRefNo) DEEsclatedToACRA
FROM(
Select T.CaseRefNo, T.TaskCaseRefNo, (dbo.funcGetWorkingDays(T.CaseReceivedDate, 
		(select MIN(TH.CreatedDate) from tblTaskHistory TH 
			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
			Where TE.TeamTypeID = 2 And TH.TaskID = T.TaskID)
		)) NoOfWorkingDays
From tblCaseProfileIndex CPI 
INNER JOIN tblTask T ON CPI.CaseProfileIndex = T.CaseProfileIndex
Where CPI.CaseSourceID = 2 And CPI.IsReplyCase = 0 And CPI.CreatedDate Between @StartDate And @EndDate
And T.TaskID IN (Select Top 1 TaskID from tblTaskHistory TH 
			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
			Where TE.TeamTypeID = 2 And TH.TaskID = T.TaskID Order By TaskHistoryID)) T
Group By T.NoOfWorkingDays
Order By T.NoOfWorkingDays



--select count(*) from tblCaseProfileIndex CPI
--INNER JOIN tblUser U ON U.UserID = CPI.ClosedBy
--INNER JOIN tblTeam T ON T.TeamID = U.TeamID
----Where Convert(varchar(8), CPI.ClosedOn, 112) Between '20131226' And '20140704'
--Where CPI.ClosedOn Between '26-Dec 2013 6:00 PM' And '27-Jan-2014 6:00 PM'
--And CPI.CaseSourceID IN (1,2) And CPI.IsReplyCase = 0 And CPI.CaseStatus = 100
--And T.TeamTypeID = 1

--select count(*) from tblCaseProfileIndex CPI
--INNER JOIN tblUser U ON U.UserID = CPI.ClosedBy
--INNER JOIN tblTeam T ON T.TeamID = U.TeamID
--Where CPI.ClosedOn Between '26-Dec 2013 6:00 PM' And '27-Jan-2014 6:00 PM'
--And CPI.CaseSourceID IN (1,2) And CPI.NoOfWorkingDays <= 3 And CPI.IsReplyCase = 0 And CPI.CaseStatus = 100
--And T.TeamTypeID = 1


select dbo.funcGetLastWorkingDateTime('01-Jul-2014')


--SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
--FROM sys.procedures
--WHERE OBJECT_DEFINITION(object_id) LIKE '%funcGetLastWorkingDateTime%'